package com.turing.post.salesmanage.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.salesmanage.util.DepositOrder;
import com.turing.post.salesmanage.util.DepositOrderDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * 充值单管理
 * 
 * @author zxp
 * 
 */
@Stateless(mappedName = "salesmanage.impl.DepositOrderDaoImpl")
@Remote
@Local
public class DepositOrderDaoImpl implements DepositOrderDao {

	@PersistenceContext
	protected EntityManager _em;

	@Override
	public DepositOrder getDepositOrderByDepositOrderId(
			BigInteger depositOrderId) {
		return _em.find(DepositOrder.class, depositOrderId);
	}

	@SuppressWarnings("unchecked")
	@Override
	public DepositOrder getDepositOrderForUpdate(BigInteger depositOrderId) {
		Query query = _em
				.createNativeQuery(
						"select t.* from DepositOrder t where t.DepositOrderId = ?1 for update",
						DepositOrder.class);
		query.setParameter(1, depositOrderId);
		List<DepositOrder> li = query.getResultList();
		if (li == null || li.size() != 1)
			return (null);
		DepositOrder ao = li.get(0);
		_em.refresh(ao);
		return (ao);
	}

	@SuppressWarnings("unchecked")
	@Override
	public DepositOrder getDepositOrderBySaleOrderSeqNo(String saleOrderSeqNo) {
		Query query = _em.createNativeQuery(
				"select t.* from DepositOrder t where t.SaleOrderSeqNo = ?1",
				DepositOrder.class);
		query.setParameter(1, saleOrderSeqNo);
		List<DepositOrder> li = query.getResultList();
		if (li == null || li.size() != 1)
			return (null);
		return li.get(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositOrder> getDepositOrderListByCondition(
			PCPSWebInput webInput, String depositOrderId,
			String saleBranchNo, String depositType,
			BigInteger startSaleAmount, BigInteger endSaleAmount,
			String startTime, String endTime, String orderStatus,
			String btnStatus,String feeType, Integer page) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		if (page.intValue() == -1) {
			maxResult = 36000;
			startPosition = 0;
		}
		// 平台管理员无法查询
		String sql = "select t.* from DepositOrder t where (?7 is null or saleorderseqno=?7) and (?1 is null or DepositType=?1) and "
				+ "(?2 is null or to_number(TotalDepositAmount)>=?2) and (?3 is null or TotalDepositAmount<=?3) and "
				+ "(?4 is null or DepositTime>=to_date(?4,'yyyy-mm-dd hh24:mi:ss')) and "
				+ "(?5 is null or DepositTime<=to_date(?5,'yyyy-mm-dd hh24:mi:ss')) and (?6 is null or OrderStatus=?6)";
		if (!PCPSUtil.isEmpty(feeType)) {
			sql += " and depositOrderId in(select depositOrderId from DepositOrderPaidDetail t where paidtype='"
					+ feeType
					+ "' and TranTime>=to_date('"
					+ startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss') and TranTime<= to_date('"
					+ endTime + " 23:59:59','yyyy-mm-dd hh24:mi:ss'))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			sql = getSql(btnStatus, sql);
		}
		sql += " order by DepositTime desc";
		Query query = _em.createNativeQuery(sql, DepositOrder.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, depositType);
		query.setParameter(2, startSaleAmount);
		query.setParameter(3, endSaleAmount);
		query.setParameter(4, startTime + " 00:00:00");
		query.setParameter(5, endTime + " 23:59:59");
		query.setParameter(6, orderStatus);
		query.setParameter(7, depositOrderId);

		List<DepositOrder> orderList = query.getResultList();
		List<DepositOrder> depositOrderList = new ArrayList<DepositOrder>();
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			for (DepositOrder so : orderList) {
				if (btnStatus.equals(DepositOrder.BtnStatus_Edit)) {
					so.setBtnStatus(DepositOrder.BtnStatus_Edit);
					depositOrderList.add(so);
				} else if (btnStatus.equals(DepositOrder.BtnStatus_Cancel)) {
					so.setBtnStatus(DepositOrder.BtnStatus_Cancel);
					depositOrderList.add(so);
				} else if (btnStatus.equals(DepositOrder.BtnStatus_Sales)) {
					so.setBtnStatus(DepositOrder.BtnStatus_Sales);
					depositOrderList.add(so);
				} else if (btnStatus
						.equals(DepositOrder.BtnStatus_InputConfirm)) {
					if (so.getOrderStatus().equals(
							DepositOrder.DEPORDER_ORDERSTATUS_INPUT)) {
						so.setBtnStatus(DepositOrder.BtnStatus_InputConfirm);
					} else if (so.getOrderStatus().equals(
							DepositOrder.DEPORDER_ORDERSTATUS_INPUTCONFIRM)) {
						so
								.setBtnStatus(DepositOrder.BtnStatus_InputConfirmCancel);
					}
					depositOrderList.add(so);
				} else if (btnStatus.equals(DepositOrder.BtnStatus_Paid)) {
					if (so.getOrderStatus().equals(
							DepositOrder.DEPORDER_ORDERSTATUS_HAVEPAID)) {
						so.setBtnStatus(DepositOrder.BtnStatus_PaidOffCancel);
					} else if (so.getOrderStatus().equals(
							DepositOrder.DEPORDER_ORDERSTATUS_INPUTCONFIRM)) {
						if (so.getTotalDepositAmount().compareTo(
								so.getAlreayPaidAmount()) != 0) {
							so.setBtnStatus(DepositOrder.BtnStatus_Paid);
						} else if (so.getTotalDepositAmount().compareTo(
								so.getAlreayPaidAmount()) == 0) {
							so.setBtnStatus(DepositOrder.BtnStatus_Paid
									+ DepositOrder.BtnStatus_PaidOff);
						}
					}
					depositOrderList.add(so);
				} else if (btnStatus.equals(DepositOrder.BtnStatus_Account)) {
					if (so.getOrderStatus().equals(
							DepositOrder.DEPORDER_ORDERSTATUS_HAVEPAID)) {
						so.setBtnStatus(DepositOrder.BtnStatus_Account);
					} else if (so.getOrderStatus().equals(
							DepositOrder.DEPORDER_ORDERSTATUS_HAVERECHARGE)) {
						so.setBtnStatus(DepositOrder.BtnStatus_AccountCan);
					}
					depositOrderList.add(so);
				} else if (btnStatus.equals(DepositOrder.BtnStatus_Auth)) {
					if (so.getOrderStatus().equals(
							DepositOrder.DEPORDER_ORDERSTATUS_HAVERECHARGE)) {
						so.setBtnStatus(DepositOrder.BtnStatus_Auth);
					}
					depositOrderList.add(so);
				}
			}
			// 循环结束返回
			return depositOrderList;
		}
		return orderList;
	}

	private String getSql(String btnStatus, String sql) {
		if (btnStatus.equals(DepositOrder.BtnStatus_Edit)
				|| btnStatus.equals(DepositOrder.BtnStatus_Sales)) {
			sql = sql + " and t.OrderStatus ='"
					+ DepositOrder.DEPORDER_ORDERSTATUS_INPUT + "'";
		} else if (btnStatus.equals(DepositOrder.BtnStatus_Cancel)) {
			sql = sql + " and t.OrderStatus in('"
					+ DepositOrder.DEPORDER_ORDERSTATUS_INPUT + "','"
					+ DepositOrder.DEPORDER_ORDERSTATUS_INPUTCONFIRM + "')";
		} else if (btnStatus.equals(DepositOrder.BtnStatus_InputConfirm)) {
			sql = sql + " and t.OrderStatus in ('"
					+ DepositOrder.DEPORDER_ORDERSTATUS_INPUT + "','"
					+ DepositOrder.DEPORDER_ORDERSTATUS_INPUTCONFIRM + "')";
		} else if (btnStatus.equals(DepositOrder.BtnStatus_Paid)) {
			sql = sql + " and t.OrderStatus in('"
					+ DepositOrder.DEPORDER_ORDERSTATUS_INPUTCONFIRM + "','"
					+ DepositOrder.DEPORDER_ORDERSTATUS_HAVEPAID + "')";
		} else if (btnStatus.equals(DepositOrder.BtnStatus_Account)) {
			sql = sql + " and t.OrderStatus in('"
					+ DepositOrder.DEPORDER_ORDERSTATUS_HAVEPAID + "','"
					+ DepositOrder.DEPORDER_ORDERSTATUS_HAVERECHARGE + "')";
		}
		return sql;
	}

	@Override
	public BigInteger getDepositOrderTotalByCondition(PCPSWebInput webInput,
			String depositOrderId, String saleBranchNo, String depositType,
			BigInteger startSaleAmount, BigInteger endSaleAmount,
			String startTime, String endTime, String orderStatus,
			String btnStatus,String feeType) {
		String sql = "select nvl(count(*),0) from DepositOrder t where (?7 is null or saleorderseqno=?7) and (?1 is null or DepositType=?1) and "
				+ "(?2 is null or to_number(TotalDepositAmount)>=?2) and (?3 is null or TotalDepositAmount<=?3) and "
				+ "(?4 is null or DepositTime>=to_date(?4,'yyyy-mm-dd hh24:mi:ss')) and "
				+ "(?5 is null or DepositTime<=to_date(?5,'yyyy-mm-dd hh24:mi:ss')) and (?6 is null or OrderStatus=?6)";
		if (!PCPSUtil.isEmpty(feeType)) {
			sql += " and depositOrderId in(select depositOrderId from DepositOrderPaidDetail t where paidtype='"
					+ feeType
					+ "' and TranTime>=to_date('"
					+ startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss') and TranTime<= to_date('"
					+ endTime + " 23:59:59','yyyy-mm-dd hh24:mi:ss'))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			sql = getSql(btnStatus, sql);
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, depositType);
		query.setParameter(2, startSaleAmount);
		query.setParameter(3, endSaleAmount);
		if (startTime != null) {
			startTime = startTime + " 00:00:00";
		}
		if (endTime != null) {
			endTime = endTime + " 23:59:59";
		}
		query.setParameter(4, startTime);
		query.setParameter(5, endTime);
		query.setParameter(6, orderStatus);
		query.setParameter(7, depositOrderId);
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public void insertDepositOrder(DepositOrder depositOrder)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, depositOrder);
	}

	@Override
	public void updateDepositOrder(DepositOrder depositOrder)
			throws PCPSApplicationException {
		GeneralDao.update(_em, depositOrder);
	}

	/**
	 * 卡-4 充值统计表
	 * 
	 * @param webInput
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardBatchDepositReportByCondition(
			PCPSWebInput webInput, String branchNo, String startTime,
			String endTime) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(branchNo))
			return null;
		String sqlcond = PCPSUtil.getSqlCond(webInput, branchNo,
				"a.SaleBranchNo");
		String sql = "select a.salebranchno,t.salebranchname,sum(b.depositamount) totalamount,count(*) totalcount,c.cardfacename,a.remark"
				+ " from depositorder a,depositorderdetail b,salebranch t,cardface c,makecardcontrol m"
				+ " where a.salebranchno=t.salebranchno and a.depositorderid=b.depositorderid"
				+ " and b.batchno = m.batchno and m.cardfaceid = c.cardfaceid"
				+ " and a.orderstatus='"
				+ DepositOrder.DEPORDER_ORDERSTATUS_HAVERECHARGE
				+ "'"
				+ sqlcond
				+ " and a.LastDepositDate >= to_date(?1,'yyyy-MM-dd')"
				+ " and a.LastDepositDate <= to_date(?2,'yyyy-MM-dd')"
				+ " group by a.salebranchno,t.salebranchname,c.cardfacename,a.remark order by a.salebranchno ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startTime);
		query.setParameter(2, endTime);
		List<Object[]> list = query.getResultList();
		return list;
	}

	/**
	 * 卡充值明细表
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardBatchDepositDetailReportByCondition(
			PCPSWebInput webInput, String branchNo, String startTime,
			String endTime) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(branchNo))
			return null;
		String sqlcond = PCPSUtil.getSqlCond(webInput, branchNo,
				"t.SaleBranchNo");
		String sql = "select t.depositorderid,t.saleorderseqno,b.salebranchname,to_char(t.deposittime,'yyyy-mm-dd hh24:mi:ss') deposittime,t.orderstatus,t.remark"
				+ " from depositorder t,salebranch b where t.salebranchno=b.salebranchno"
				+ sqlcond
				+ " and t.LastDepositDate >= to_date(?1,'yyyy-mm-dd')"
				+ " and t.LastDepositDate <= to_date(?2,'yyyy-mm-dd')"
				+ " and OrderStatus='"
				+ DepositOrder.DEPORDER_ORDERSTATUS_HAVERECHARGE
				+ "' order by t.salebranchno,t.deposittime,t.orderstatus";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startTime);
		query.setParameter(2, endTime);
		List<Object[]> list = query.getResultList();
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositOrder> getDepositOrderListByVoucherNo(String voucherNo) {
		// 平台管理员无法查询
		String sql = "select t.* from DepositOrder t where CommitVoucherNo=?1";
		Query query = _em.createNativeQuery(sql, DepositOrder.class);
		query.setParameter(1, voucherNo);
		return query.getResultList();
	}

	@Override
	public Object[] getDepositOrderListByConditionSum(PCPSWebInput webInput,
			String depositOrderId, String saleBranchNo, String depositType,
			BigInteger startSaleAmount, BigInteger endSaleAmount,
			String startTime, String endTime, String orderStatus,
			String btnStatus,String feeType) {
		// 平台管理员无法查询
		String sql = "select nvl(sum(TotalDepositAmount),0),nvl(sum(AlreayPaidAmount),0),nvl(sum(FactFee),0) from DepositOrder t where (?7 is null or saleorderseqno=?7) and (?1 is null or DepositType=?1) and "
				+ "(?2 is null or to_number(TotalDepositAmount)>=?2) and (?3 is null or TotalDepositAmount<=?3) and "
				+ "(?4 is null or DepositTime>=to_date(?4,'yyyy-mm-dd hh24:mi:ss')) and "
				+ "(?5 is null or DepositTime<=to_date(?5,'yyyy-mm-dd hh24:mi:ss')) and (?6 is null or OrderStatus=?6)";
		if (!DepositOrder.DEPORDER_ORDERSTATUS_CANCEL.equals(orderStatus)) {
			sql = sql + " and t.orderStatus<>'" + DepositOrder.DEPORDER_ORDERSTATUS_CANCEL + "' ";
		}
		if (!PCPSUtil.isEmpty(feeType)) {
			sql += " and depositOrderId in(select depositOrderId from DepositOrderPaidDetail t where paidtype='"
					+ feeType
					+ "' and TranTime>=to_date('"
					+ startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss') and TranTime<= to_date('"
					+ endTime + " 23:59:59','yyyy-mm-dd hh24:mi:ss'))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, depositType);
		query.setParameter(2, startSaleAmount);
		query.setParameter(3, endSaleAmount);
		query.setParameter(4, startTime + " 00:00:00");
		query.setParameter(5, endTime + " 23:59:59");
		query.setParameter(6, orderStatus);
		query.setParameter(7, depositOrderId);

		Object[] obj = (Object[]) query.getSingleResult();
		return obj;
	}

	@Override
	public BigInteger getDepositOrderFactFeeBySaleBrchNos(String saleBrchNos,
			String startDate, String endDate) {
		String sql = "select nvl(sum(t.factFee),0) from depositorder t"
				+ " where t.salebranchno in (" + saleBrchNos + ")"
				+ " and t.trandate >=to_date(?1,'yyyy-MM-dd')"
				+ " and t.trandate <=to_date(?2,'yyyy-MM-dd')"
				+ " and t.OrderStatus <> 'C'";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		return new BigInteger(query.getSingleResult().toString());
	}
}